
/**
 * Date:2019年6月6日上午10:32:36
 * Copyright (c) 2019, www.bwbroad.com All Rights Reserved.
 *
*/

package com.xue.im.common.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.AbstractListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.xue.im.common.vo.UserMessage;

/**
 * Description: TODO <br/>
 * Date: 2019年6月6日 上午10:32:36 <br/>
 * 
 * @author xuejianxin
 */
public class DB {

	static {
		try {
			Class.forName("org.h2.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	private static Connection getConn() throws SQLException {
		return DriverManager.getConnection("jdbc:h2:D:/java/work20181111/myIM/data/myIM2019", "sa", "123456");
	}

	public static void main(String[] args) throws Exception {

		// http://commons.apache.org/proper/commons-dbutils/examples.html
		ResultSetHandler<List<Object[]>> h = new AbstractListHandler<Object[]>() {
			@Override
			protected Object[] handleRow(ResultSet rs) throws SQLException {
				ResultSetMetaData meta = rs.getMetaData();
				int cols = meta.getColumnCount();
				Object[] result = new Object[cols];

				for (int i = 0; i < cols; i++) {
					result[i] = rs.getObject(i + 1);
				}
				return result;
			}
		};

		// 直接返回bean
		ResultSetHandler<List<UserMessage>> h1 = new BeanListHandler<>(UserMessage.class);

		QueryRunner run = new QueryRunner();
		try (Connection conn = getConn()) {
			// Execute the query and get the results back from the handler
//			List<Object[]> result = run.query(conn, "SELECT * FROM user_message where owner like ?", h, "xue");
//			for (Object[] o : result) {
//				System.out.println(Arrays.toString(o));
//			}
			List<UserMessage> result = run.query(conn, "SELECT * FROM user_message where owner like ?", h1, "xue");
			for (UserMessage o : result) {
				System.out.println(o);
			}

			// int inserts = run.update( "INSERT INTO Person (name,height) VALUES
			// (?,?)","John Doe", 1.82 );

			// int updates = run.update( "UPDATE Person SET height=? WHERE name=?",2.05,
			// "John Doe" );
			// SCRIPT TO 'fileName'

			// st.execute( "drop all objects;");// 这一句可以不要
            //执行脚本
			String testSqlPath = DB.class.getResource("test.sql").toURI().toString().substring(6);
			System.out.println(testSqlPath);
			// run.execute(conn,"drop table IF EXISTS test ");
			// run.execute(conn,"drop table IF EXISTS test1 ");
			run.execute(conn, "runscript from '" + testSqlPath + "'");
			// 备份数据库
			run.execute(conn, "SCRIPT TO 'D:/java/work20181111/myIM/data/dump.sql'");

			// run.execute(conn," insert into test1 values('111','name111')");
			List<Object[]> result1 = run.query(conn, "select * from  test1 order by id desc limit 2", h);
			for (Object[] o : result1) {
				System.out.println(Arrays.toString(o));
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}
